package sql;

import java.sql.*;
import java.util.*;

public class TransactionLookUp 
{
	DataAccess dataAccess = new DataAccess();
	
	public void createBookTransaction(String currentDate, Integer buyerId, Integer buyerAccountId) throws ClassNotFoundException, SQLException
	{
		String query = "INSERT INTO BOOK_TRANSACTION(DATE_PURCHASED, BUYER_ID, BUYER_ACCOUNT_ID)" +
        			    " VALUES(to_date('" +currentDate  +"', 'mm/dd/yyyy')"  +"," +buyerId +"," +buyerAccountId +")";	
		dataAccess.modifyTable(query);
	}
	
	public Integer getBookTransaction(Integer buyerId, Integer buyerAccountId) throws ClassNotFoundException, SQLException
	{
		String query = "SELECT MAX(TRANSACTION_NUMBER)" +
        			    " FROM BOOK_TRANSACTION" +
                       " WHERE BUYER_ID = " +buyerId +
                         " AND BUYER_ACCOUNT_ID = " +buyerAccountId;		
		
		ResultSet rset = dataAccess.getSelectResult(query);
		
		Integer transactionNumber = 0;		
		while(rset.next())
		{
			transactionNumber = rset.getInt(1);
		}		
		return transactionNumber;		
	}
	
	public void createTransactionLine(Integer transactionNumber, ArrayList<Integer> cartBookId) throws ClassNotFoundException, SQLException
	{
		String query = "";
		//shipping is hard coded since it wasn't retrieved when book information was retrieved
		Integer shippingId = 2; 
		
		for(Integer bookId: cartBookId)
		{
			query = "INSERT INTO TRANSACTION_LINE(TRANSACTION_NUMBER, BOOK_ID, SHIPPING_ID) "+ 
			       " VALUES( "+transactionNumber +"," +bookId +"," +shippingId +")";			
			dataAccess.modifyTable(query);
		}        
	}
	
	public void updateBookStatus(ArrayList<Integer> cartBookId) throws ClassNotFoundException, SQLException
	{
		String query="";
		for(Integer bookId: cartBookId)
		{
			query = "UPDATE BOOK" +
			          " SET IS_SOLD = 'Y'"+
			        " WHERE BOOK_ID = "+bookId;
			dataAccess.modifyTable(query);
		}      
	}
	
	public void addFinance(Integer accountId, double payment) throws ClassNotFoundException, SQLException
	{
		String query ="UPDATE FINANCE_ACCOUNT "+
             	        " SET BALANCE = BALANCE + " +payment +
                      " WHERE ACCOUNT_ID = "+accountId;
		dataAccess.modifyTable(query);
	}
	
	public void deductFinance(Integer accountId, double payment) throws ClassNotFoundException, SQLException
	{
		String query ="UPDATE FINANCE_ACCOUNT "+
             	        " SET BALANCE = BALANCE - " +payment +
                      " WHERE ACCOUNT_ID = "+accountId;
		dataAccess.modifyTable(query);
	}
}
